﻿using System;
using System.Data;
using MySql.Data.MySqlClient;
using SHARED;
using System.Collections.Generic;


namespace SHARED
{
    public class MYSQL : IDb
    {
        MySqlConnection conn = new MySqlConnection();

        public MYSQL(string serverName, string dbName, string userID, string pwd)
        {
            conn.ConnectionString = "Server=" + serverName + ";" +
                                    "Database=" + dbName + ";" +
                                    "Uid=" + userID + ";" +
                                    "Pwd=" + pwd + ";";

        }
        /// <summary>
        /// Gets all the column names of a given table
        /// </summary>
        /// <param name="TableName">table name</param>
        /// <returns>L</returns>
        public List<string> GetColumnNames(string TableName)
        {
            List<string> columnNames = new List<string>();
            string query = "Select top 1 * from " + TableName;
            MySqlCommand mysqlCommand = null;
            try
            {
                conn.Open();
                mysqlCommand = new MySqlCommand(query, conn);
                mysqlCommand.CommandType = CommandType.Text;
                MySqlDataReader mysqlReader = mysqlCommand.ExecuteReader(CommandBehavior.KeyInfo);
                DataTable schema = mysqlReader.GetSchemaTable();
                for (int i = 0; i < schema.Rows.Count; i++)
                {
                    columnNames.Add(schema.Rows[i][0].ToString());
                }
                mysqlReader.Close();
                return columnNames;

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }

        }

        /// <summary>
        /// Checks if the connection can be established given user's input
        /// </summary>
        /// <returns>true, if the connection is established successfully. Otherwise, it returns false</returns>
        public bool isConnected()
        {
            try
            {
                using (conn)
                {
                    conn.Open();
                    return true;
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// Executes a given select statement and builds a tree of MasterElements
        /// </summary>
        /// <param name="command"></param>
        /// <param name="listM"></param>
        /// <param name="root"></param>
        /// <returns></returns>
        public MasterElementList ExecuteSelectCommandObject(IDbCommand command, MasterElementList listM, MasterElement root)
        {
            //DataTable result = new DataTable();
            MasterElementList result = new MasterElementList(root);
            MySqlCommand mySqlCommand = null;
            try
            {
                mySqlCommand = (MySqlCommand)command;
                mySqlCommand.Connection = conn;
                mySqlCommand.Connection.Open();
                mySqlCommand.CommandType = CommandType.Text;
                MySqlDataReader mySqlReader = mySqlCommand.ExecuteReader(CommandBehavior.KeyInfo);

                DataTable schema = mySqlReader.GetSchemaTable();

                //int fields = 0;
                for (int j = 0; j < listM.Count; j++)
                {
                    for (int k = 0; k < listM[j].elements.Count; k++)
                    {
                        if (listM[j].elements[k].isTagOnly == false)
                        {
                            for (int i = 0; i < schema.Rows.Count; i++)
                            {
                                if (schema.Rows[i][0].ToString().ToLower().Trim() == listM[j].elements[k].tagName.ToLower().Trim())
                                {
                                    listM[j].elements[k].tagType = schema.Rows[i]["DataType"].ToString();
                                    listM[j].elements[k].tableName = schema.Rows[i]["BaseTableName"].ToString();
                                    listM[j].elements[k].columnSize = schema.Rows[i]["ColumnSize"].ToString();
                                    listM[j].elements[k].baseColumnName = schema.Rows[i]["BaseColumnName"].ToString();
                                    break;
                                }
                            }

                        }
                    }
                }


                while (mySqlReader.Read())
                {
                    List<MasterElement> mEl = new List<MasterElement>();
                    for (int i = 0; i < listM.Count; i++)
                    {
                        MasterElement temp = (MasterElement)listM[i].Clone();
                        for (int j = 0; j < temp.elements.Count; j++)
                        {
                            if (temp.elements[j].isTagOnly == false)
                            {
                                temp.elements[j].tagValue = mySqlReader[temp.elements[j].tagName];
                                //temp.elements[j].tagType = sqlReader[te;
                            }

                        }
                        mEl.Add(temp);

                    }
                    for (int i = 0; i < mEl.Count; i++)
                    {

                        //set parent
                        if (i == 0)
                        {
                            mEl[i].Parent = root;
                        }
                        else
                        {
                            for (int j = 0; j < i; j++)
                            {
                                if (mEl[i].level - 1 == mEl[j].level)
                                {
                                    mEl[i].Parent = mEl[j];
                                    break;
                                }
                            }
                        }
                        //add children
                        if (i != mEl.Count - 1)
                        {
                            for (int j = i; j < mEl.Count; j++)
                            {
                                if (mEl[i].level + 1 == mEl[j].level)
                                {
                                    mEl[i].AddChild(mEl[j]);
                                }
                            }
                        }

                    }
                    for (int i = 0; i < mEl.Count; i++)
                    {
                        result.Add(mEl[i]);

                    }


                }
                mySqlReader.Close();
                return result;
            }
            catch
            {
                throw;
            }
            finally
            {
                mySqlCommand.Connection.Close();
            }

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="selectStatement"></param>
        /// <returns></returns>
        public System.Data.DataTable ExecuteSelectCommand(string selectStatement)
        {
            DataTable result = new DataTable();
            try
            {
                conn.Open();
                MySqlCommand mysqlCommand = new MySqlCommand(selectStatement);
                mysqlCommand.CommandType = CommandType.Text;
                MySqlDataReader mySqlReader = mysqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
                result.Load(mySqlReader);
                mySqlReader.Close();
                return result;

            }
            catch (Exception)
            {

                throw;
            }
        }

        public System.Data.DataTable ExecuteSelectCommand(string selectStatement, string[,] parameters)
        {
            DataTable result = new DataTable();
            try
            {
                MySqlCommand mysqlCommand = new MySqlCommand(selectStatement);

                for (int i = 0; i < parameters.Length; i++)
                {
                    mysqlCommand.Parameters.AddWithValue(parameters[i, 0], parameters[0, i]); //parameters[name, value]
                }

                conn.Open();
                MySqlDataReader mysqlReader = mysqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
                result.Load(mysqlReader);
                mysqlReader.Close();
                return result;
            }
            catch (Exception)
            {

                throw;
            }
        }

    }
}
